﻿using System.Collections;
using System.Collections.Generic;
using UnityEngine;
using UnityEditor;
using OfficeOpenXml;
using System.IO;
using System;
using System.Text;

/// <summary>
/// Excel导出Lua工具
/// Excel配置规则(只读取sheet1,Sheet1的命名是导出Lua table的命名)
/// 第一行描述 
/// 第二行标记是否导出Lua字段或是sql 
/// 第三行数据类型(string,int,float,bool,int[]) 
/// 第四行字段名称(如果读取excel第n列发现第4行字段为空则不导出)
/// 第五行正式数据配置...
/// </summary>
public class ExcelExportTool
{
    private const int dataRowIndex = 5;
    private const string path = "Assets/Res/Arts/ExcelConfig";

    [MenuItem("Assets/策划工具/Excel/Excel导出Lua", false, 5000)]
    public static void exportExcel()
    {
        if (!Directory.Exists(path))
        {
            Debug.LogError("excel路径不存在" + path);
            return;
        }
        UnityEngine.Object[] objs = Selection.objects;
        bool hasObj = objs != null && objs.Length > 0;
        if (hasObj)
        {
            for (int i = 0; i < objs.Length; i++)
            {
                if (File.Exists(Path.Combine(path, objs[i].name + ".xlsx")))
                {
                    exportExcelCS(Path.Combine(path, objs[i].name + ".xlsx"));
                }
            }
        }
        else
        {
            string[] files = Directory.GetFiles(path, "*xlsx");
            for (int i = 0; i < files.Length; i++)
            {
                exportExcelCS(files[i]);
            }
        }
        AssetDatabase.Refresh(ImportAssetOptions.ForceUpdate);
        AssetDatabase.SaveAssets();
        EditorUtility.DisplayDialog("导出完成", "导出Excel完成", "OK");
    }
    //读取excel
    public static void exportExcel(string path)
    {
        using (ExcelPackage package = new ExcelPackage(new FileStream(path, FileMode.Open, FileAccess.Read, FileShare.ReadWrite)))
        {
            Dictionary<int, List<string>> dict = new Dictionary<int, List<string>>();
            ExcelWorksheet sheet = package.Workbook.Worksheets[1];
            int maxRow = sheet.Dimension.End.Row;
            if (maxRow >= dataRowIndex)
            {
                for (int i = 1; i <= maxRow; i++)
                {
                    //如果当前行 第一个元素为空 continue                    
                    object val = sheet.GetValue(i, 1);
                    if (i >= dataRowIndex && (val == null || string.IsNullOrEmpty(val.ToString()))) continue;
                    List<string> lst = new List<string>();
                    readRow(sheet, i, ref lst);
                    dict.Add(i, lst);
                }
                writeTest(dict, sheet.Name);
            }
            else
            {
                Debug.LogError("配置表格式有问题 ");
            }
        }
    }

    //读取一行
    public static void readRow(ExcelWorksheet sheet, int index, ref List<string> lst)
    {
        int maxCol = sheet.Dimension.End.Column;
        for (int i = 1; i <= maxCol; i++)
        {
            //拿当前列 第4行 字段为空 跳过
            object proVal = sheet.GetValue(4, i);
            if (proVal == null || string.IsNullOrEmpty(proVal.ToString())) continue;

            object val = sheet.GetValue(index, i);
            lst.Add(val != null ? val.ToString() : "");
        }
    }
    //写入一整张表
    private static string expPath = Path.Combine(Application.dataPath, "LuaScripts/Scripts/Config");
    public static void writeTest(Dictionary<int, List<string>> dict, string tableName)
    {
        if (!Directory.Exists(expPath))
            Directory.CreateDirectory(expPath);
        string exp = Path.Combine(expPath, tableName + ".lua");
        if (File.Exists(exp)) File.Delete(exp);

        FileStream fs = new FileStream(exp, FileMode.Append);
        StreamWriter sw = new StreamWriter(fs);
        string calss = tableName + " = { }\n";
        sw.Write(calss);
        for (int i = dataRowIndex; i <= dict.Count; i++)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append(tableName + "[" + dict[i][0] + "]={");
            List<string> lst = dict[i];
            for (int j = 0; j < lst.Count; j++)
            {
                string dataClass = dict[3][j];
                if (dataClass == "int" || dataClass == "float" || dataClass == "long")
                {
                    lst[j] = string.IsNullOrEmpty(lst[j]) ? "0" : lst[j];
                }
                else if (dataClass == "string")
                {
                    lst[j] = "'" + lst[j] + "'";
                }
                else if (dataClass == "bool")
                {
                    int boolVal = 0;//bool变量 0false 1true 
                    if (int.TryParse(lst[j], out boolVal))
                    {
                        lst[j] = boolVal == 0 ? "false" : "true";
                    }
                    else
                    {
                        StringBuilder bbuilder = new StringBuilder();
                        bbuilder.Append("'");
                        bbuilder.Append(lst[j]);
                        bbuilder.Append("'");
                        lst[j] = bbuilder.ToString();
                    }
                }
                else if (dataClass == "int[]" || dataClass == "float[]")
                {
                    string[] spLst = lst[j].Split(',');
                    StringBuilder spBuilder = new StringBuilder();
                    spBuilder.Append("{");
                    for (int k = 0; k < spLst.Length; k++)
                    {
                        if (k > 0) spBuilder.Append(',');
                        spBuilder.Append(spLst[k]);
                    }
                    spBuilder.Append("}");
                    lst[j] = spBuilder.ToString();
                }
                builder.Append(dict[4][j] + "=" + lst[j]);
                if (j < lst.Count - 1)
                {
                    builder.Append(",");
                }
            }
            builder.Append("}");
            sw.Write(builder.ToString() + '\n');
        }
        sw.Flush();
        sw.Close();
        fs.Close();
    }

    /// <summary>
    /// 
    /// </summary>
    [MenuItem("Assets/策划工具/Excel/Excel导出C#", false, 5001)]
    public static void exportExcelCS()
    {
        if (!Directory.Exists(path))
        {
            Debug.LogError("excel路径不存在" + path);
            return;
        }
        UnityEngine.Object[] objs = Selection.objects;
        bool hasObj = objs!=null&& objs.Length > 0;
        if (hasObj) {
            for (int i = 0; i < objs.Length; i++)
            {
                if (File.Exists(Path.Combine(path, objs[i].name + ".xlsx")))
                {
                    exportExcelCS(Path.Combine(path, objs[i].name + ".xlsx"));
                }
            }
        }
        else
        {
            string[] files = Directory.GetFiles(path, "*xlsx");
            for (int i = 0; i < files.Length; i++)
            {
                exportExcelCS(files[i]);
            }
        }
        AssetDatabase.Refresh(ImportAssetOptions.ForceUpdate);
        AssetDatabase.SaveAssets();
        EditorUtility.DisplayDialog("导出完成", "导出Excel完成", "OK");
    }
    //读取excel
    public static void exportExcelCS(string path)
    {
        using (ExcelPackage package = new ExcelPackage(new FileStream(path, FileMode.Open, FileAccess.Read, FileShare.ReadWrite)))
        {
            Dictionary<int, List<string>> dict = new Dictionary<int, List<string>>();
            ExcelWorksheet sheet = package.Workbook.Worksheets[1];
            int maxRow = sheet.Dimension.End.Row;
            if (maxRow >= dataRowIndex)
            {
                for (int i = 1; i <= maxRow; i++)
                {
                    //如果当前行 第一个元素为空 continue                    
                    object val = sheet.GetValue(i, 1);
                    if (i >= dataRowIndex && (val == null || string.IsNullOrEmpty(val.ToString()))) continue;
                    List<string> lst = new List<string>();
                    readRow(sheet, i, ref lst);
                    dict.Add(i, lst);
                }
                exportCSExcel(dict, sheet.Name);
            }
            else
            {
                Debug.LogError("配置表格式有问题C# ");
            }
        }
    }

    //导出CS
    /// <summary>
    /// Excel导出Lua工具
    /// Excel配置规则(只读取sheet1,Sheet1的命名是导出Lua table的命名)
    /// 第一行描述 
    /// 第二行标记是否导出Lua字段或是sql 
    /// 第三行数据类型(string,int,float,bool,int[]) 
    /// 第四行字段名称(如果读取excel第n列发现第4行字段为空则不导出)
    /// 第五行正式数据配置...
    /// </summary>
    private static string expCSPath = Path.Combine(Application.dataPath, "Scripts/Modulus/Config");//"G:/LuaScripts/Scripts";// Path.Combine("G:", "LuaScripts /Scripts/Config");
    public static void exportCSExcel(Dictionary<int, List<string>> dict, string tableName)
    {
        if (!Directory.Exists(expCSPath))
            Directory.CreateDirectory(expCSPath);
        string exp = Path.Combine(expCSPath, tableName + ".cs");
        if (File.Exists(exp)) File.Delete(exp);
        string tableMapName = tableName + "Map";
        FileStream fs = new FileStream(exp, FileMode.Append);
        StreamWriter sw = new StreamWriter(fs);
        StringBuilder builder = new StringBuilder();
        //头部
        builder.Append("using System.Collections.Generic;"); builder.AppendLine();
        builder.Append("using ConfigMap;"); builder.AppendLine();
        builder.Append("using Config;"); builder.AppendLine();
        builder.Append("namespace Config"); builder.AppendLine();
        builder.Append("{"); builder.AppendLine();        
        builder.AppendFormat("public class {0}", tableName); builder.AppendLine();
        builder.Append("{"); builder.AppendLine();
        //成员声明
        List<string> memberLst = dict[1];
        StringBuilder memberBuilder = new StringBuilder();
        memberBuilder.AppendFormat("   public {0} (", tableName);
        StringBuilder mLineBuilder = new StringBuilder();
        for (int i = 0; i < memberLst.Count; i++)
        {
            string typeName = getTypeName(dict[3][i].ToLower());
            string memberName = dict[4][i].ToString();
            //声明成员变量
            builder.AppendFormat("   public {0}  {1};", typeName, memberName); builder.AppendLine();
            //构造函数
            if (i >= memberLst.Count - 1)
            {
                memberBuilder.AppendFormat("{0} {1})", typeName, memberName);
                memberBuilder.Append("{"); builder.AppendLine();
            }
            else
            {
                memberBuilder.AppendFormat("{0} {1},", typeName, memberName);
            }
            //构造函数赋值 
            mLineBuilder.AppendFormat("     this.{0} = {1};", memberName, memberName); mLineBuilder.AppendLine();
        }
        builder.Append(memberBuilder.ToString()); builder.AppendLine();
        builder.Append(mLineBuilder.ToString()); builder.AppendLine();
        builder.Append("   }"); builder.AppendLine();
        //以上Config类声明
        //Config类函数声明
        builder.AppendFormat("   public static {0} get(int key)", tableName); builder.AppendLine();
        builder.Append("   {"); builder.AppendLine();
        builder.AppendFormat("      return {0}.Instance.get(key);", tableMapName); builder.AppendLine();
        builder.Append("   }"); builder.AppendLine();
        builder.AppendFormat("   public static Dictionary<int, {0}> getAll()", tableName); builder.AppendLine();
        builder.Append("   {"); builder.AppendLine();
        builder.AppendFormat("      return {0}.Instance.getAll();", tableMapName); builder.AppendLine();
        builder.Append("   }"); builder.AppendLine();
        builder.Append("   }"); builder.AppendLine();
        builder.Append("}"); builder.AppendLine();
        //以上Config类声明完成
        builder.Append("namespace ConfigMap"); builder.AppendLine();
        builder.Append("{"); builder.AppendLine();
        //字典类
        builder.AppendFormat("public class {0}: Singleton<{1}>", tableMapName, tableMapName); builder.AppendLine();
        builder.Append("{"); builder.AppendLine();
        builder.AppendFormat("   public Dictionary<int, {0}> map = null; ", tableName); builder.AppendLine();
        builder.AppendFormat("   protected override void initialize() ", tableName); builder.AppendLine();
        builder.Append("   {"); builder.AppendLine();
        builder.AppendFormat("      map = new Dictionary<int, {0}>(); ", tableName); builder.AppendLine();
        //开始插入对象
        for (int i = dataRowIndex; i <= dict.Count; i++)
        {
            List<string> lst = dict[i];
            int key = int.Parse(lst[0]);
            StringBuilder addBuilder = new StringBuilder();
            for (int j = 0; j < lst.Count; j++)
            {
                string dataClass = getTypeName(dict[3][j]);
                string str = j >= lst.Count - 1 ? "{0}" : "{0},";
                if (dataClass.ToLower() == "int")
                {
                    string num = string.IsNullOrEmpty(lst[j]) ? "0" : lst[j];
                    addBuilder.AppendFormat(str, int.Parse(num));
                }
                else if (dataClass.ToLower() == "long")
                {
                    string num = string.IsNullOrEmpty(lst[j]) ? "0" : lst[j];
                    addBuilder.AppendFormat(str, long.Parse(num));
                }
                else if (dataClass.ToLower() == "float")
                {
                    string num = string.IsNullOrEmpty(lst[j]) ? "0" : lst[j];
                    str = j >= lst.Count - 1 ? "{0}f" : "{0}f,";
                    addBuilder.AppendFormat(str, float.Parse(num));
                }
                else if (dataClass.ToLower() == "double")
                {
                    string num = string.IsNullOrEmpty(lst[j]) ? "0" : lst[j];
                    addBuilder.AppendFormat(str, double.Parse(num));
                }
                else if (dataClass.ToLower() == "bool")
                {
                    string num = string.IsNullOrEmpty(lst[j]) ? "0" : lst[j];
                    int val = 1;
                    if (int.TryParse(num, out val))
                    {
                        num = val == 0 ? "false" : "true";
                    }
                    addBuilder.AppendFormat(str, num);
                }
                else if (dataClass.ToLower() == "string")
                {
                    string num = string.IsNullOrEmpty(lst[j]) ? "" : lst[j];
                    str = j >= lst.Count - 1 ? "\"{0}\"" : "\"{0}\",";
                    addBuilder.AppendFormat(str, num);
                }
                else if (dataClass.ToLower() == "int[]")
                {
                    string intStr = string.IsNullOrEmpty(lst[j]) ? "" : lst[j];
                    StringBuilder spBuilder = new StringBuilder();
                    if (string.IsNullOrEmpty(intStr))
                    {
                        spBuilder.Append("null");
                    }
                    else
                    {
                        string[] spLst = intStr.Split(',');
                        spBuilder.Append("new int[] {");
                        for (int k = 0; k < spLst.Length; k++)
                        {
                            if (k > 0) spBuilder.Append(',');
                            spBuilder.Append(spLst[k]);
                        }
                        spBuilder.Append("}");
                    }
                    addBuilder.AppendFormat(str, spBuilder.ToString());
                }
                else if (dataClass.ToLower() == "float[]")
                {
                    string intStr = string.IsNullOrEmpty(lst[j]) ? "" : lst[j];
                    StringBuilder spBuilder = new StringBuilder();
                    if (string.IsNullOrEmpty(intStr))
                    {
                        spBuilder.Append("null");
                    }
                    else
                    {
                        string[] spLst = intStr.Split(',');
                        spBuilder.Append("new float[] {");
                        for (int k = 0; k < spLst.Length; k++)
                        {
                            if (k > 0) spBuilder.Append(',');
                            spBuilder.Append(spLst[k]);
                            spBuilder.Append("f");
                        }
                        spBuilder.Append("}");
                    }
                    addBuilder.AppendFormat(str, spBuilder.ToString());
                }
                else if (dataClass.ToLower() == "string[]")
                {
                    string intStr = string.IsNullOrEmpty(lst[j]) ? "" : lst[j];
                    StringBuilder spBuilder = new StringBuilder();
                    if (string.IsNullOrEmpty(intStr))
                    {
                        spBuilder.Append("null");
                    }
                    else
                    {
                        string[] spLst = intStr.Split(',');

                        spBuilder.Append("new string[] {");
                        for (int k = 0; k < spLst.Length; k++)
                        {
                            if (k > 0) spBuilder.Append(',');
                            spBuilder.AppendFormat("\"{0}\"", spLst[k]);
                        }
                        spBuilder.Append("}");
                    }
                    addBuilder.AppendFormat(str, spBuilder.ToString());
                }
                else if (dataClass.ToLower() == "dictionary<int,int>")
                {
                    //字典
                    string dictStr = string.IsNullOrEmpty(lst[j]) ? "" : lst[j];
                    StringBuilder spBuilder = new StringBuilder();
                    if (string.IsNullOrEmpty(dictStr) || !dictStr.Contains("{"))
                    {
                        spBuilder.Append("null");
                    }
                    else
                    {
                        //new Dictionary<int, int>() { { 1,2} };
                        spBuilder.Append("new Dictionary<int, int>() { ");
                        dictStr = dictStr.Replace("{", "");
                        string[] spLst = dictStr.Split('}');
                        for (int k = 0; k < spLst.Length; k++)
                        {
                            string[] nums = spLst[k].Split(',');
                            if (nums == null || nums.Length <= 1) continue;
                            int num1 = int.Parse(nums[0]);
                            int num2 = int.Parse(nums[1]);
                            spBuilder.Append("{");
                            spBuilder.AppendFormat("{0},{1}", num1, num2);
                            spBuilder.Append("},");
                        }
                        spBuilder.Append("}");
                    }
                    addBuilder.AppendFormat(str, spBuilder.ToString());
                }
            }
            //插入
            builder.AppendFormat("      map.Add({0},new {1}({2}));", key, tableName, addBuilder.ToString()); builder.AppendLine();
        }
        builder.Append("}"); builder.AppendLine();

        //插入对象完毕
        //函数声明
        builder.AppendFormat("   public {0} get(int key)", tableName); builder.AppendLine();
        builder.Append("   {"); builder.AppendLine();
        builder.AppendFormat("      {0} cfg;", tableName); builder.AppendLine();
        builder.AppendFormat("     if (map.TryGetValue(key, out cfg))"); builder.AppendLine();
        builder.Append("   {"); builder.AppendLine();
        builder.Append("        return cfg;"); builder.AppendLine();
        builder.Append("   }"); builder.AppendLine();
        builder.Append("   return null;"); builder.AppendLine();
        builder.Append("   }"); builder.AppendLine();
        builder.AppendFormat("   public  Dictionary<int, {0}> getAll()", tableName); builder.AppendLine();
        builder.Append("   {"); builder.AppendLine();
        builder.AppendFormat("      return map;", tableMapName); builder.AppendLine();
        builder.Append("   }"); builder.AppendLine();
        builder.Append("}"); builder.AppendLine();

        builder.Append("}"); builder.AppendLine();

        sw.Write(builder.ToString());
        sw.Flush();
        sw.Close();
        fs.Close();

    }

    private static string getTypeName(string name)
    {
        if (name.ToLower() == "dict" || name.ToLower() == "dictionary")
        {
            return "Dictionary<int,int>";
        }
        else
        {
            return name.ToLower();
        }
    }

}